import os
import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'student.settings')
django.setup(set_prefix=False)
from django.db.models import Q,Avg,Sum,Aggregate,IntegerField,Expression,Sum,F,Count,Max
from stu.models import Student,Score

stu_mgr = Student.objects
sco_mgr = Score.objects

def no_to_list(args):
    return [v["stu_no"] for v in args]

# 查询总分大于250的学生
#1.
# quryset = sco_mgr.all()
# for i in quryset.values():
#     if (i['math'] + i['chinese'] + i['english']) > 250:
#         print(stu_mgr.filter(pk=i['stu_no']).first())
#2.
# print(stu_mgr.filter(stu_no__in=no_to_list(sco_mgr.annotate(c=(F('chinese') + F('math') + F('english'))).filter(c__gt=250).values('stu_no'))))

# print(sco_mgr.filter(pk__gt=10010).values('pk').annotate(c=Count('pk')))#  查询语文成绩在80-90之间的学生信息
# print(sco_mgr.all().values('pk',total=F('math')+F('chinese')+F('english')))
#print(sco_mgr.filter(pk=1).count())
#SELECT COUNT(*) AS `__count` FROM `score` WHERE `score`.`stu_no` = 1

# print(sco_mgr.all().values('pk').annotate(Count('pk')))
print(sco_mgr.all().values('pk').annotate(Avg('math')).values('math__avg'))
#1
# queryset = sco_mgr.filter(chinese__gte=80,chinese__lte=90).values("stu_no")
# for i in queryset:
#     print(stu_mgr.get(pk=i.stu_no))
#2
# queryset = sco_mgr.filter(chinese__gte=80,chinese__lte=90).values("stu_no")
# print(stu_mgr.filter(pk__in=no_to_list(queryset)))

# 查询任意一门课程小于60的学生信息
# queryset = sco_mgr.filter(Q(chinese__lt=60)|Q(math__lt=60) | Q(english__lt=60))
# for i in queryset:
#     print(stu_mgr.get(pk=i.stu_no))

# 查询平均分>70分的学生信息
#1.
# class ScoreAvg: # 求传入数值平均值
#     def __int__(self):
#         pass
#     def __call__(self,*args):
#         return sum(args)/len(args)
#
# queryset=sco_mgr.all()
# for i in queryset:
#     if ScoreAvg()(i.chinese,i.math,i.english) > 70:
#             print(stu_mgr.get(pk=i.stu_no))

#2.
# print(stu_mgr.filter(stu_no__in=no_to_list(sco_mgr.annotate(avg_t=(F( 'math')+F('chinese')+F('english'))/3).filter(avg_t__gt=70).values('stu_no'))))
# 三门成绩都大于90分的学生姓名
#queryset=sco_mgr.filter(chinese__gte=90,math__gte=90,english__gte=90)
#for i in queryset:
#    print(stu_mgr.get(pk=i.stu_no))

